﻿using QiHe.Office.CompoundDocumentFormat;
using QiHe.Office.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;

namespace Tyng.Data.Excel
{
    /// <summary>
    /// IDataReader for excel files (97-2003) without interop.
    /// </summary>
    public class ExcelDataReader : CustomDataReader
    {
        CompoundDocument _doc;
        Workbook _book;
        Field[] _fields;

        //TODO: use worksheets as multiple result sets? if so need to identify each worksheets columns independently, probably not worthwhile, just open new readers
        int _worksheetIndex = 0;
        int _rowIndex = 0;

        #region "Constructors"

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileStream"></param>
        /// <param name="worksheetIndex"></param>
        /// <param name="hasHeaders"></param>
        /// <param name="rowsToSkip"></param>
        /// <param name="fields"></param>
        public ExcelDataReader(Stream fileStream, int worksheetIndex, bool hasHeaders, int rowsToSkip, params Field[] fields) : this(fileStream, worksheetIndex)
        {
            _rowIndex += rowsToSkip;

            _fields = fields;

            ReadStrings();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileStream"></param>
        /// <param name="worksheetIndex"></param>
        public ExcelDataReader(Stream fileStream, int worksheetIndex)
        {
            if (fileStream == null) throw new ArgumentNullException("fileStream");

            _worksheetIndex = worksheetIndex;

            _doc = CompoundDocument.Read(fileStream);

            byte[] bookdata = _doc.GetStreamData("Workbook");

            _book = new Workbook();
            _book.Read(new MemoryStream(bookdata));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="worksheetIndex"></param>
        /// <param name="maxRows"></param>
        public ExcelDataReader(string fileName, int worksheetIndex, int maxRows) : base(maxRows)
        {
            if (string.IsNullOrEmpty(fileName)) throw new ArgumentNullException("fileName");

            _worksheetIndex = worksheetIndex;

            _doc = CompoundDocument.Read(fileName);

            byte[] bookdata = _doc.GetStreamData("Workbook");

            _book = new Workbook();
            _book.Read(new MemoryStream(bookdata));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="worksheetIndex"></param>
        /// <param name="hasHeaders"></param>
        /// <param name="rowsToSkip"></param>
        public ExcelDataReader(string fileName, int worksheetIndex, bool hasHeaders, int rowsToSkip)
            : this(fileName, worksheetIndex, int.MaxValue)
        {
            _rowIndex += rowsToSkip;

            if (hasHeaders)
            {
                string[] columnHeadings = ReadStrings();

                _fields = new Field[columnHeadings.Length];

                for (int i = 0; i < columnHeadings.Length; i++)
                {
                    string header = columnHeadings[i];

                    if (string.IsNullOrEmpty(header)) header = string.Format("Column{0}", i);

                    _fields[i] = new Field(header);
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="worksheetIndex"></param>
        /// <param name="hasHeaders"></param>
        /// <param name="rowsToSkip"></param>
        /// <param name="maxRows"></param>
        public ExcelDataReader(string fileName, int worksheetIndex, bool hasHeaders, int rowsToSkip, int maxRows)
            : this(fileName, worksheetIndex, maxRows)
        {
            _rowIndex += rowsToSkip;

            if (hasHeaders)
            {
                string[] columnHeadings = ReadStrings();

                _fields = new Field[columnHeadings.Length];

                for (int i = 0; i < columnHeadings.Length; i++)
                {
                    string header = columnHeadings[i];

                    if (string.IsNullOrEmpty(header)) header = string.Format("Column{0}", i);

                    _fields[i] = new Field(header);
                }
            }
        }

        #endregion

        /// <summary>
        /// 
        /// </summary>
        public CompoundDocument CompoundDocument
        {
            get
            {
                if (IsClosed) throw new InvalidOperationException("Reader is closed");

                return _doc;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public Workbook Workbook
        {
            get
            {
                if (IsClosed) throw new InvalidOperationException("Reader is closed");

                return _book;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public Worksheet Worksheet
        {
            get
            {
                if (IsClosed) throw new InvalidOperationException("Reader is closed");

                return _book.Worksheets[_worksheetIndex];
            }
        }

        private string[] ReadStrings()
        {
            List<string> headings = new List<string>(Worksheet.MaxColIndex);

            for (int i = 0; i <= Worksheet.MaxColIndex; i++)
            {
                Cell c = Worksheet.Cells[_rowIndex, i];

                if (c != null)
                    headings.Add(Worksheet.Cells[_rowIndex, i].StringValue);
                else
                    headings.Add(string.Empty); //not sure this is right...
            }

            _rowIndex++;

            return headings.ToArray();
        }

        private object[] ReadLine()
        {
            if (_rowIndex > Worksheet.MaxRowIndex)
                return null;

            List<object> cellValues = new List<object>(DataFields.Length);

            for (int i = 0; i < DataFields.Length; i++)
            {
                Cell c = Worksheet.Cells[_rowIndex, i];

                if (c == null)
                    cellValues.Add(null);
                else
                {
                    if (DataFields[i].DataType == typeof(DateTime))
                        cellValues.Add(Worksheet.Cells[_rowIndex, i].DateTimeValue);
                    else
                        cellValues.Add(DataFields[i].ConvertFromString(Worksheet.Cells[_rowIndex, i].StringValue));
                }
            }

            _rowIndex++;

            return cellValues.ToArray();
        }

        private void LoadAutoFields()
        {
            string[] values = ReadStrings();

            _rowIndex--; //backup, just peeking...

            if (values == null) return;

            List<Field> fieldList = new List<Field>(values.Length);

            for (int i = 0; i < values.Length; i++)
            {
                fieldList.Add(new Field(string.Format("Column{0}", i), typeof(string)));
            }

            _fields = fieldList.ToArray();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        protected override IDataRecord GetNextDataRecord()
        {
            if (RecordsAffected == -1 && _fields == null) LoadAutoFields();

            object[] record = ReadLine();

            if (_fields.Length > 0 && record == null)
                return null;

            return new ObjectArrayDataRecord(record, _fields);
        }

        /// <summary>
        /// 
        /// </summary>
        protected override Field[] DataFields
        {
            get
            {
                if (_fields == null)
                    LoadAutoFields();

                return _fields;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public override bool IsClosed
        {
            get { return _doc == null; }
        }

        /// <summary>
        /// 
        /// </summary>
        public override void Close()
        {
            base.Close();

            _doc = null;
            _book = null;
        }
    }
}
